차근차근정확하게

누적 카운터 집계 함정

Properties1
tags학습, 개발-cs, 데이터베이스, sql, 집계

7 min read

누적 카운터 집계 함정

NOTE

장비/센서 등이 “누적값”을 계속 보내오는 데이터에서 기간별 증분을 집계할 때 흔히 걸리는 세 가지 함정 — 잘못된 소스 테이블 선택, 합산 후 차감의 위험, MAX(CASE...) 오용 — 과 해결 패턴. 실무(장비 추출 횟수 집계)에서 추출·일반화.

배경 — “누적값”을 다루는 통계의 공통 구조

일부 장비/카테고리는 절대 카운터를 계속 누적해서 보고한다(리셋 전까지 계속 증가). 특정 기간의 “그 기간 동안 발생한 증분”을 구하려면 보통 (기간 종료 시점 누적값) - (기간 시작 시점 누적값)을 계산한다. 이 단순한 계산이 아래 세 가지 이유로 자주 어긋난다.

함정 1 — 이미 합산된 테이블을 쓰면 세부 축이 사라진다

원본 데이터가 “카테고리 A + 카테고리 B + … 단위로 이미 합산된” 요약 테이블과, “세부 카테고리별로 나뉜” 원천 테이블 두 가지로 존재할 수 있다. 세부 카테고리가 중간에 바뀐 이력이 있는 경우, 이미 합산된 요약 테이블만으로는 정확한 증분을 복원할 수 없다 — 합산 시점에 세부 축 정보가 이미 사라졌기 때문이다.

해결: 세부 카테고리 축이 유지되는 원천(raw) 누적 테이블을 기준으로 집계한다. 합산 테이블은 “이미 계산된 결과를 빠르게 보여주는 캐시” 정도로만 쓰고, 정확한 재계산이 필요하면 원천 테이블로 내려간다.

함정 2 — “합산 후 차감”은 한 축의 이상치가 다른 축을 오염시킨다

여러 세부 카테고리(축)의 누적값을 각각 “최종 - 최초”로 구하지 않고, 전체를 먼저 더한 뒤 한 번에 빼면 문제가 생긴다.

잘못된 방식: SUM(최종 누적 전체) - SUM(최초 누적 전체)

이 방식은 특정 축 하나에서 누적값 리셋, 데이터 누락, 역전(최종값 < 최초값) 이 발생하면, 그 축의 오류가 다른 정상 축의 값까지 깎아먹는다 — 전체를 먼저 더해버렸기 때문에 어느 축이 문제인지 구분할 수 없고 합계 전체가 왜곡된다.

해결: 축(카테고리)별로 먼저 “최종-최초” 차분을 계산 →그 다음에 축들을 합산하는 순서로 바꾼다.

-- ① 축별 최초/최종 스냅샷 추출 (ROW_NUMBER로 순서 부여)
WITH BASE AS (
    SELECT category_key, seq_value,
           ROW_NUMBER() OVER (PARTITION BY category_key ORDER BY event_time ASC)  AS rn_first,
           ROW_NUMBER() OVER (PARTITION BY category_key ORDER BY event_time DESC) AS rn_last
      FROM raw_cumulative_table
     WHERE event_time BETWEEN #{fromTime} AND #{toTime}
),
-- ② 축별로 먼저 차분 계산 + 음수 방어(리셋/역전 시 0으로 바닥)
DIFF AS (
    SELECT a.category_key,
           GREATEST(b.seq_value - a.seq_value, 0) AS delta
      FROM BASE a
      JOIN BASE b ON a.category_key = b.category_key
     WHERE a.rn_first = 1 AND b.rn_last = 1
)
-- ③ 이제 축들을 합산해도 한 축의 이상치가 다른 축을 오염시키지 않음
SELECT SUM(delta) AS total_delta FROM DIFF;

GREATEST(diff, 0)로 리셋·역전 케이스를 0으로 바닥 처리해 음수가 합계에 섞이는 것도 함께 방어한다.

함정 3 — MAX(CASE WHEN 축=N THEN 값 END)는 피벗용이지 합계용이 아니다

축별로 값을 컬럼으로 펼치는(피벗) 관용구로 MAX(CASE WHEN ...)을 쓰는 경우가 있는데, 이건 “그 축에 값이 하나뿐”이라는 전제가 있을 때만 안전하다. 그 축 안에 여러 하위 항목(세부 카테고리 등)이 섞여 있으면 가장 큰 값 하나만 살아남고 나머지는 버려진다.

-- 위험 — 축 안에 하위 항목이 여러 개면 그 중 최댓값 하나만 남음
MAX(CASE WHEN axis = 1 THEN value END) AS axis1_value

해결: 피벗이 아니라 합계가 필요하면 SUM(CASE WHEN ...)으로 바꾼다.

-- 올바름 — 축 안의 모든 하위 항목 값을 합산
SUM(CASE WHEN axis = 1 THEN value ELSE 0 END) AS axis1_total

요약 원칙

  1. 누적값 집계는 가능하면 세부 축이 살아있는 원천 테이블을 기준으로 한다(이미 합산된 요약 테이블에 의존하지 않는다).
  2. 여러 축의 누적값을 뺄셈으로 증분화할 때는 축별로 먼저 차분 → 그 다음 합산 순서를 지킨다(전체를 먼저 더한 뒤 한 번에 빼지 않는다). 리셋/역전 가능성이 있으면 GREATEST(diff, 0)로 방어한다.
  3. MAX(CASE WHEN...)은 “그 축에 값이 유일할 때”만 안전한 피벗 관용구다 — 합계가 필요하면 SUM(CASE WHEN...)을 쓴다.

그래프 뷰